import time
from .mysqlconn import conn
from .mongodbconnection import mongorobot
import json
import sys

#查询包含的公司编码
def getcmpid(dbname,cid):
    sql = " SELECT c.COMPANYID from zt_company c  ORDER BY c.COMPANYID;"
    cmpidlist = conn.mysql_sel(sql,dbname,cid)
    return cmpidlist


#查询需要学习客服的聊天记录
def studyagent(cmpid,dbname,cid):
    sql = " select (select  max(tl.msg) from zt_service_cust_textchatlog tl  \
         where tl.scustid=ct.scustid and tl.sendtime<ct.sendtime   \
          AND tl.sendtime=(select  max(tl.sendtime) from zt_service_cust_textchatlog tl \
          where tl.scustid=ct.scustid and tl.sendtime<ct.sendtime   \
           and left(tl.fromid,1)='c' and left(tl.toid,6)!='aRobot' and left(tl.toid,1)='a' and tl.msgtype='text'  ) \
           and left(tl.fromid,1)='c' and left(tl.toid,6)!='aRobot' and left(tl.toid,1)='a' and tl.msgtype='text'  ) as questiorn \
           ,CONCAT('b',ct.sourceid),ct.id \
            from zt_service_cust sc ,zt_service_cust_textchatlog ct \
            where sc.ServiceID=ct.scustid and  sc.cmpid='"+str(cmpid)+"'  \
           and (TO_DAYS(now()) -TO_DAYS(ct.sendtime))=1  \
          and left(ct.fromid,1)='a'  and left(ct.toid,1)='c' and left(ct.fromid,6)!='aRobot'  and ct.sourceid is not null ; "
    study = conn.mysql_sel(sql,dbname,cid)
    return study


#将机器人学习客服的数据保存到数据库
def insertstudy(cmpid,question,answerid,state,dbname,cid):
    sql =" INSERT into zy_robotstudy(cmpid,question,answerid,state,inserttime) VALUES \
     ('"+ str(cmpid) +"' ,"+ json.dumps(question, ensure_ascii=False) +" ,"+ json.dumps(answerid, ensure_ascii=False) +" ,"+ str(state) +",now()); "
    count = conn.mysql_insert(sql,dbname,cid)
    return count

